identifier

Table Name: identifier

The identifier table stores unique identifiers associated with entities. Each identifier is tied to a specific type, location, and entity (mappedId), enabling flexible and detailed record management.

---

Columns

Column Name	Data Type	Constraints	Description
identifierId	int(11)	Primary Key, Auto Increment	A unique system-generated identifier for each record in the table.
identifier	varchar(255)	Not Null, Unique	The unique identifier value associated with an entity.
locationId	int(11)	Nullable, Foreign Key	Links the identifier to a specific location, referencing the location table.
preferred	tinyint(1)	Nullable	Indicates whether the identifier is the preferred one for the entity (1 for true, 0 for false).
mappedId	int(11)	Not Null, Foreign Key	Links the identifier to an entity in the idmapper table.
identifierTypeId	int(11)	Not Null, Foreign Key	Specifies the type of identifier, referencing the identifiertype table.

---

Indexes

1. Primary Key: identifierId
   • Ensures uniqueness of each record.
2. Unique Key: identifier_UNIQUE
   • Guarantees that the identifier value is unique across the table.
3. Foreign Key Indexes:
   • identifier_locationId_location_locationId_FK: Optimizes lookups for locationId.
   • identifier_identTypeId_identifierType_identTypeId_FK: Optimizes lookups for identifierTypeId.
   • identifier_mappedId_idmapper_mappedId_FK: Optimizes lookups for mappedId.

---

Foreign Key Relationships

1. idmapper table:
   • The mappedId column references idmapper(mappedId).
   • Links the identifier to a unique entity in the system.
2. identifiertype table:
   • The identifierTypeId column references identifiertype(identifierTypeId).
   • Specifies the type/category of the identifier (e.g., national ID, passport number).
3. location table:
   • The locationId column references location(locationId).
   • Associates the identifier with a specific location (if applicable).

---

Usage Notes

• Purpose:
  • The identifier table serves as a registry for managing unique identifiers tied to entities within the system.
  • Supports multiple identifiers per entity, with the ability to mark one as the preferred identifier.
• Preferred Field:
  • The preferred column allows systems to identify and prioritize a specific identifier for an entity when multiple are present.
• Integration:
  • This table integrates with the idmapper table for entity management and the identifiertype table for categorization of identifiers.
• Uniqueness:
  • The identifier column is constrained to be unique, ensuring no duplication of identifier values across records.

---